package org.zjx.swing.db;

import org.zjx.swing.bean.ColorRecord;

import java.util.List;

public class ColorRecordDB {
    private static boolean isTableCreated = false;
    private SQLiteHelper dbHelper;

    public ColorRecordDB() {
        dbHelper = SQLiteHelper.getSingleton();
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;
        }
    }

    // 建表语句（自动包含自增主键id）
    public void createTable() {
        String sql =
                "CREATE TABLE IF NOT EXISTS color_record (\n" +
                        "  id          INTEGER PRIMARY KEY AUTOINCREMENT,\n" +  // 自增主键
                        "  checked     INTEGER NOT NULL DEFAULT 0,\n" +          // boolean转INTEGER
                        "  r           INTEGER NOT NULL,\n" +
                        "  g           INTEGER NOT NULL,\n" +
                        "  b           INTEGER NOT NULL,\n" +
                        "  deviationR  INTEGER NOT NULL,\n" +
                        "  deviationG  INTEGER NOT NULL,\n" +
                        "  deviationB  INTEGER NOT NULL\n" +
                        ");";
        dbHelper.createTable(sql);
    }

    // 插入数据（若颜色+偏差组合存在，则更新checked字段）
    public void insert(ColorRecord record) {
        String sql =
                "INSERT INTO color_record (checked, r, g, b, deviationR, deviationG, deviationB)\n" +
                        "VALUES (?, ?, ?, ?, ?, ?, ?)\n" +
                        "ON CONFLICT(r, g, b, deviationR, deviationG, deviationB)\n" +  // 需要表中设置UNIQUE约束
                        "DO UPDATE SET checked = excluded.checked";                     // 冲突时更新checked
        Object[] params = {
                record.isChecked() ? 1 : 0,  // boolean转SQLite INTEGER
                record.getR(),
                record.getG(),
                record.getB(),
                record.getDeviationR(),
                record.getDeviationG(),
                record.getDeviationB()
        };
        dbHelper.executeUpdate(sql, params);
    }

    // 根据id删除
    public void deleteById(int id) {
        String sql = "DELETE FROM color_record WHERE id = ?";
        dbHelper.executeUpdate(sql, new Object[]{id});
    }

    // 根据颜色+偏差删除
    public void deleteByColor(int r, int g, int b, int dr, int dg, int db) {
        String sql = "DELETE FROM color_record WHERE r=? AND g=? AND b=? AND deviationR=? AND deviationG=? AND deviationB=?";
        Object[] params = {r, g, b, dr, dg, db};
        dbHelper.executeUpdate(sql, params);
    }

    // 根据id查询
    public ColorRecord findById(int id) {
        String sql = "SELECT * FROM color_record WHERE id = ?";
        List<ColorRecord> result = dbHelper.executeQuery(sql, new Object[]{id}, ColorRecord.class);
        return result.isEmpty() ? null : result.get(0);
    }

    // 根据颜色+偏差查询
    public ColorRecord findByColor(int r, int g, int b, int dr, int dg, int db) {
        String sql = "SELECT * FROM color_record WHERE r=? AND g=? AND b=? AND deviationR=? AND deviationG=? AND deviationB=?";
        Object[] params = {r, g, b, dr, dg, db};
        List<ColorRecord> result = dbHelper.executeQuery(sql, params, ColorRecord.class);
        return result.isEmpty() ? null : result.get(0);
    }

    // 查询所有记录
    public List<ColorRecord> findAll() {
        String sql = "SELECT * FROM color_record";
        return dbHelper.executeQuery(sql, new Object[]{}, ColorRecord.class);
    }

    // 根据id更新记录
    public void updateById(int id, ColorRecord newRecord) {
        String sql = "UPDATE color_record SET checked=?, r=?, g=?, b=?, deviationR=?, deviationG=?, deviationB=? WHERE id=?";
        Object[] params = {
                newRecord.isChecked() ? 1 : 0,
                newRecord.getR(),
                newRecord.getG(),
                newRecord.getB(),
                newRecord.getDeviationR(),
                newRecord.getDeviationG(),
                newRecord.getDeviationB(),
                id
        };
        dbHelper.executeUpdate(sql, params);
    }

    // 清空表
    public void clearData() {
        dbHelper.clearData("color_record");
    }

    // 删除表
    public void dropTable() {
        dbHelper.dropTable("color_record");
    }
}
